﻿''' <summary>
''' clsLedger类的数据操作类
''' 对应表S_Ledger
''' 对应类clsLedger
''' Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\自写系统\rjcwxt\Rjcwxt\data\sys.mdb;Persist Security Info=True
''' </summary>
Public Class ClsLedgerDbAccess
    Inherits ClsBaseDbAccess

    Public Sub New(ByVal s As String)
        MyBase.New(s, TABLE_NAME_LEDGER)
    End Sub

    Public Sub New(ByVal s As String, ByVal n As String)
        MyBase.New(s, n)
    End Sub

    'Private mConnectionString As String '数据库连接字符串
    'Private mLedgerTableName As String 'S_User在库中的表名 S_Ledger


    ''' <summary>
    ''' 删除指定账套
    ''' </summary>
    ''' <param name="l">账套类对象</param>
    ''' <returns>0表示失败，非0表示 成功</returns>
    Public Function DeleteLedger(ByVal l As ClsLedger) As Int32
        Try

            Dim tmpSql As String
            Dim tmpDbAccess = GetConnection() 'New ClsDbAccessMdb(mConnectionString)
            Dim tmpParas(0) As OleDb.OleDbParameter
            Dim tmpReturn As Int32 = 0
            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "delete * from [" & Me.AppTableName & "] where [LedgerId] = @LedgerId" ' & sqlCondition
            '构造参数
            tmpParas(0) = New OleDb.OleDbParameter("@Ledger", OleDb.OleDbType.Integer)
            '参数赋值
            tmpParas(0).Value = l.LedgerId
            '执行删除查询
            tmpReturn = tmpDbAccess.UpdateRecord(tmpSql, tmpParas)
            '关闭数据库
            tmpDbAccess.DbClose()

            Return tmpReturn

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "clsLedgerDbAccess - DeleteLedger"

            Return 0
        End Try
    End Function

    ''' <summary>
    ''' 返回指定条件的账套集合
    ''' </summary>
    ''' <param name="sqlCondition">指定的查询条件</param>
    ''' <returns>账套列表集合List(Of clsLedger)</returns>
    Public Function GetLedgerByCondition(ByVal sqlCondition As String) As List(Of ClsLedger)
        Dim tmpList As List(Of ClsLedger) = New List(Of ClsLedger)()

        Try

            Dim tmpSql As String
            Dim tmpDbAccess = GetConnection() 'New ClsDbAccessMdb(mConnectionString)
            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "select * from [" & Me.AppTableName & "] where " & sqlCondition

            '查询并将返回数据解析为List对象
            tmpList = CType(ConvertRecordToClass(tmpDbAccess.ExecuteSqlReturnDataSet(tmpSql)), List(Of ClsLedger))

            '关闭数据库
            tmpDbAccess.DbClose()


        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "clsLedgerDbAccess - GetAllLedger"

            'Return Nothing
        End Try

        Return tmpList
    End Function

    ''' <summary>
    ''' 插入表数据
    ''' </summary>
    ''' <param name="l">要插入的ClsLedger类对象</param>
    ''' <returns>非0值 ，如果是插入则返回新播放的autoIncrementId</returns>
    Public Function InsertLedger(ByVal l As ClsLedger) As Int32
        Try

            Dim tmpSql As String
            Dim tmpDbAccess = GetConnection() ' New ClsDbAccessMdb(mConnectionString)
            Dim tmpInsertParas(2) As OleDb.OleDbParameter
            'Dim tmpUpdateParas(3) As OleDb.OleDbParameter
            Dim tmpReturn As Int32
            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "Insert into [" & Me.AppTableName & "]"
            tmpSql &= " ([LedgerName],[LedgerCompanyName],[LedgerDataRealPath]) values("
            tmpSql &= "@LedgerName,@LedgerCompanyName,@LedgerDataRealPath"
            tmpSql &= ")"
            '构造参数集合

            tmpInsertParas(0) = New OleDb.OleDbParameter("@LedgerName", OleDb.OleDbType.VarWChar)
            tmpInsertParas(1) = New OleDb.OleDbParameter("@LedgerCompanyName", OleDb.OleDbType.VarWChar)
            tmpInsertParas(2) = New OleDb.OleDbParameter("@LedgerDataRealPath", OleDb.OleDbType.VarWChar)

            tmpInsertParas(0).Value = l.LedgerName
            tmpInsertParas(1).Value = l.LedgerCompanyName
            tmpInsertParas(2).Value = l.LedgerDataRealPath

            '查询并将返回数据解析为List对象
            tmpReturn = (tmpDbAccess.InsertData(tmpSql, tmpInsertParas, True))

            '关闭数据库
            tmpDbAccess.DbClose()

            Return tmpReturn

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "clsLedgerDbAccess - InsertLedger"

            Return 0
        End Try
    End Function

    ''' <summary>
    ''' 更新表数据
    ''' </summary>
    ''' <param name="l">要更新的ClsLedger类对象</param>
    ''' <returns>非0值</returns>
    Public Function UpdateLedger(ByVal l As ClsLedger) As Int32
        Try

            Dim tmpSql As String
            Dim tmpDbAccess = GetConnection() 'New ClsDbAccessMdb(mConnectionString)
            Dim tmpUpdateParas(3) As OleDb.OleDbParameter
            Dim tmpReturn As Int32
            '打开数据库
            tmpDbAccess.DbOpen()

            '构造查询语句
            tmpSql = "update [" & Me.AppTableName & "] "
            tmpSql &= " set [LedgerName]=@LedgerName,[LedgerCompanyName]=@LedgerCompanyName,[LedgerDataRealPath]=@LedgerDataRealPath"
            tmpSql &= " where [LedgerId] = @LedgerId "

            '构造参数集合
            tmpUpdateParas(0) = New OleDb.OleDbParameter("@LedgerName", OleDb.OleDbType.VarWChar)
            tmpUpdateParas(1) = New OleDb.OleDbParameter("@LedgerCompanyName", OleDb.OleDbType.VarWChar)
            tmpUpdateParas(2) = New OleDb.OleDbParameter("@LedgerDataRealPath", OleDb.OleDbType.VarWChar)
            tmpUpdateParas(3) = New OleDb.OleDbParameter("@LedgerId", OleDb.OleDbType.Integer)

            tmpUpdateParas(0).Value = l.LedgerName
            tmpUpdateParas(1).Value = l.LedgerCompanyName
            tmpUpdateParas(2).Value = l.LedgerDataRealPath
            tmpUpdateParas(3).Value = l.LedgerId

            '查询并将返回数据解析为List对象
            tmpReturn = tmpDbAccess.UpdateRecord(tmpSql, tmpUpdateParas) '.InsertData(tmpSql, tmpUpdateParas))

            '关闭数据库
            tmpDbAccess.DbClose()

            Return tmpReturn

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "clsLedgerDbAccess - UpdateLedger"

            Return 0
        End Try
    End Function

    ''' <summary>
    ''' 返回指定编号的账套
    ''' </summary>
    ''' <param name="ledgerId">账套编号</param>
    ''' <returns>符合条件的所有账套列表集合List(Of clsLedger)</returns>
    Public Function GetLedgerById(ByVal ledgerId As Integer) As List(Of ClsLedger)
        Dim tmpList As List(Of ClsLedger) = New List(Of ClsLedger)()

        Try

            Dim tmpSql As String
            Dim tmpDbAccess = GetConnection() 'New ClsDbAccessMdb(mConnectionString)
            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "select * from [" & Me.AppTableName & "] where [LedgerId] = " & ledgerId

            '查询并将返回数据解析为List对象
            tmpList = CType(ConvertRecordToClass(tmpDbAccess.ExecuteSqlReturnDataSet(tmpSql)), List(Of ClsLedger))

            '关闭数据库
            tmpDbAccess.DbClose()


        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "clsLedgerDbAccess - GetAllLedger"

            'Return Nothing
        End Try

        Return tmpList
    End Function

    ''' <summary>
    ''' 返回所有账套
    ''' </summary>
    ''' <returns>账套列表clsLedger类的集合</returns>
    Public Function GetAllLedger() As List(Of ClsLedger)
        Dim tmpList As List(Of ClsLedger) = New List(Of ClsLedger)()
        Try

            Dim tmpSql As String
            Dim tmpDbAccess = GetConnection() 'New ClsDbAccessMdb(mConnectionString)
            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "select * from [" & Me.AppTableName & "] "

            '查询并将返回数据解析为List对象
            tmpList = CType(ConvertRecordToClass(tmpDbAccess.ExecuteSqlReturnDataSet(tmpSql)), List(Of ClsLedger))

            '关闭数据库
            tmpDbAccess.DbClose()


        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "clsLedgerDbAccess - GetAllLedger"

            'Return Nothing
        End Try

        Return tmpList

    End Function

    ''' <summary>
    ''' 将账套查询结果集转换为账套集合
    ''' </summary>
    ''' <param name="tmpTableReader">查询返回结果集</param>
    ''' <returns>账套集体List(of clsLedger)</returns>
    Public Overrides Function ConvertRecordToClass(ByVal tmpTableReader As OleDb.OleDbDataReader) As Object

        If tmpTableReader Is Nothing Then
            Return Nothing
        End If

        Dim tmpList As List(Of ClsLedger) = New List(Of ClsLedger)
        Try

            Dim tmpCls As ClsLedger

            While tmpTableReader.Read()

                tmpCls = New ClsLedger()

                With tmpCls

                    .LedgerId = tmpTableReader.GetInt32(0)
                    .LedgerName = tmpTableReader.GetString(1)
                    .LedgerCompanyName = tmpTableReader.GetString(2)
                    .LedgerDataRealPath = tmpTableReader.GetString(3)

                End With

                tmpList.Add(tmpCls)

            End While

            Return tmpList

        Catch ex As Exception

            mErrorNumber = -1001
            mErrorMessage = ex.Message
            mErrorCreater = "clsLedgerDbAccess - ConvertRecordToLedgerClass"

            Return tmpList
        End Try

    End Function

    'Public Overrides Function ConvertRecordToClass(reader As OleDb.OleDbDataReader) As Object
    '    Throw New NotImplementedException()
    'End Function
End Class
